/*
 
 TopMind jLynx "jLynx JDBC Framework"
 Copyright (c) 2004-2007. TopMind Systems Inc.
 All rights reserved.
 
 This file is part of TopMind jLynx.
 
 TopMind jLynx is free software; you can redistribute it and/or modify
 it under the terms of the License. See website for License.
 
 */
package net.sf.jlynx;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Iterator;
import java.util.Map;
import java.util.Set;
import java.util.StringTokenizer;
import java.util.TreeMap;

import javax.naming.NamingException;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 * Utility class used internally within jLynx to view/build/maintain metadata.
 * 
 * Uses JDBC specs at runtime to discover database metadata.
 * 
 * $Id: SchemaUtil.java 99 2007-06-21 02:51:13Z gritc11 $
 * 
 */
final class SchemaUtil {

	private static Connection conn;

	static Map DATABASE_INFO = new TreeMap();

	private static String entTypes[] = { "TABLE" };

	private static String keywords = null;

	private static Logger logger;

	static {

		logger = LoggerFactory.getLogger(SchemaUtil.class);
		init();

	}

	static int findDbVendor(DatabaseMetaData dm) throws SQLException {
		String dbName = dm.getDatabaseProductName();

		if ("MySQL".equalsIgnoreCase(dbName)) {
			return TransactionManager.MYSQL;
			// dm.setEscapeChar("`");
		} else if ("Microsoft SQL Server".equalsIgnoreCase(dbName))
			return TransactionManager.MSSQL;
		else if ("Oracle".equalsIgnoreCase(dbName))
			return TransactionManager.ORACLE;
		else if ("DB2".equalsIgnoreCase(dbName.substring(0, 3)))
			return (TransactionManager.DB2);
		else if ("HSQL".equalsIgnoreCase(dbName.substring(0, 4)))
			return TransactionManager.HSQL;
		else if ("PostgreSQL".equalsIgnoreCase(dbName))
			return TransactionManager.POSTGRESQL;

		return 0;
	}

	static void addConnection(ConnectionBean cb) {

		logger.info("jLynx initializing DB Connnection - " + cb.getName());

		try {
			conn = new TransactionManager().open(cb.getName());

			DatabaseMetaBean dm = new DatabaseMetaBean();

			DatabaseMetaData meta = conn.getMetaData();
			
			dm.setDbVendor(findDbVendor(meta));

			logger.info("DatabaseProductName = "
					+ meta.getDatabaseProductName());
			logger.info("DatabaseProductVersion = "
					+ meta.getDatabaseProductVersion());

			dm.setDatabase(cb.getName());

			Map tableMap = getTables(conn, cb.getSchema());

			// logger.debug("tables: " + tableMap);
			Set tables = tableMap.keySet();
			Iterator iter = tables.iterator();
			tableMap = new TreeMap();

			while (iter.hasNext()) {
				String tbl = (String) iter.next();
				Object[] pk = getPrimaryKeys(tbl).keySet().toArray();
				if (pk != null) {
					// tableMap.put(tbl.trim(), pk);
					tableMap.put(tbl.trim().toLowerCase(), pk);
					// tableMap.put(tbl.trim().toUpperCase(), pk);
				}
			}
			logger.info("jLynx DB initialization complete for '" + cb.getName()
					+ "' - Tables added: " + tableMap.keySet());
			dm.setTables(tableMap);
			DATABASE_INFO.put(cb.getName(), dm);
			logger
					.debug("jLynx - Avoid the use of database keywords as column (property) names. Keywords are: ");

			StringTokenizer st = new StringTokenizer(keywords, ",");
			while (st.hasMoreTokens()) {
				logger.debug("database keyword: " + st.nextToken());
			}

			// bug fix 6/14/2007
			conn.close();
			conn = null;

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (NamingException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

	private static void connect() throws SQLException {

		if (conn == null)
			try {
				conn = new TransactionManager().open("default");
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} catch (NamingException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
	}

	static Map getColumns(String table) throws SQLException {

		logger.debug("fetching columns for " + table);

		Map result = new TreeMap();
		connect();
		DatabaseMetaData dmd = conn.getMetaData();

		ResultSet rs = dmd.getColumns(null, null, table.toUpperCase(), null);
		// NPE 4.12.2007

		logger.debug("rs=" + rs);
		if (rs != null && !rs.next())
			throw new SQLException("No columns");

		do {

			logger.debug("col=" + rs.getObject(4));
			String col = rs.getObject(4).toString().toLowerCase();
			String type = getDataType(rs.getInt(5));
			result.put(col, type);

		} while (rs.next());

		return result;

	}

	private static String getDataType(int type) {

		Integer jType = new Integer(type);
		String result = (String) DataTypeMappings.TYPE_MAPPINGS.get(jType);

		if (result == null)
			result = "Object";

		return result;

	}

	static Map getPrimaryKeys(String table) {

		Map result = new TreeMap();
		try {
			connect();
			DatabaseMetaData dmd = conn.getMetaData();

			ResultSet rs = dmd.getPrimaryKeys(null, null, table);
			while (rs.next()) {
				String col = rs.getString(4);
				result.put(col, "Primary Key");
			}
		} catch (SQLException e) {
			logger.debug(e.getMessage());
		}

		logger.info("Entity Name = " + table + " Primary Key(s) = " + result);

		return result;

	}

	static Map getTables(Connection cn, String schema) throws SQLException {

		logger.debug("Entering cn=" + cn);

		if (schema != null)
			;// schema = schema.toUpperCase();

		Map result = new TreeMap();
		if (cn == null) {
			conn = null;
			connect();
			cn = conn;
		}
		DatabaseMetaData dmd = cn.getMetaData();
		ResultSet rs = null;
		rs = dmd.getTables(null, schema, null, entTypes);

		keywords = dmd.getSQLKeywords();

		// removed schema 3.22.05
		while (rs.next()) {
			logger.debug("getTables :: " + rs.getString(3));
			String tbl = rs.getString(3);
			// result.put(tbl.toLowerCase(), rs.getString(4));// table or view
			result.put(tbl, rs.getString(4));
		}

		// MS SQL Server system tables
		result.remove("syssegments");
		result.remove("sysconstraints");
		return result;
	}

	private static void init() {

		conn = null;

		Iterator conns = ConfigParser.getConnectionDefs().keySet().iterator();

		while (conns.hasNext()) {

			ConnectionBean cb = (ConnectionBean) ConfigParser
					.getConnectionDefs().get(conns.next());

			addConnection(cb);

		}

	}
}